<?php
/**
 * Hello Model for Hello World Component
 * 
 * @package    Joomla.Tutorials
 * @subpackage Components
 * @link http://dev.joomla.org/component/option,com_jd-wiki/Itemid,31/id,tutorials:modules/
 * @license    GNU/GPL
 */

// Check to ensure this file is included in Joomla!
defined('_JEXEC') or die();

jimport( 'joomla.application.component.model' );

/**
 * Hello Model
 *
 * @package    Joomla.Tutorials
 * @subpackage Components
 */
class bookstoreModelbookstore extends JModel
{

	 var $_data;
	 var $_buildtype;
	 //var $site_to_query;
	 var $ch; //cURL handle
	 var $_results;
	 var $_book_db;
	 var $limitstart; //offset
	 var $limit; //number of records to return
	function __construct(){
		parent::__construct();
		$this->db =& JFactory::getDBO();
		$this->_book_db = '#__sq1';
	}

	 
	 /////////////////////////////////////////////////////////////////////////////
	 /**
	 *	load i/o 'filter modules'\
	 *  TODO: add ordering mechanism so filters are applied in a heirarchy
	 */
	 
	 function loadFilters(){  //var_dump('loadfilters1');
	 	$this->filter = new stdclass();
		//$this->filter_folder = JPATH_ADMINISTRATOR.DS.'components'.DS.'com_bookstore'.DS.'filters';
		$this->filter_folder = JPATH_COMPONENT.DS.'filters';
		require_once($this->filter_folder.DS.'filter.php'); //loads abstract filter classes
		$filter = '\.extfilter\.php';//files ending in .extfilter.php; external/extention filters; basically helper classes related-to/extending filtering
		$files = JFOLDER::files($this->filter_folder, $filter, false, true); //array of fullpath names of filters
			foreach ($files as $file){	
				//$testvar = JFile::getName($file);  // ?problems with JFile::getName()? //
				$name = explode('.',basename($file));
				$base = $name[0];		
				require_once($file);
				//$class = 'filter'.ucfirst($base);
				//$this->files = $file;
				//$filter_obj =& new $class();
				// NOTE: we did create an object, but just read the file, the custom filters should actually create the new objects as necessary
				//   we simply are reading the class/functions definitions and noting that they're available for use
				$type = 'ext'; //looking for input/output/raw, etc
				$this->filter->$type->$base = 'active';			
			}
		$filter = '\.filter\.php';//files ending in .filter.php; the custom filters
		$files = JFOLDER::files($this->filter_folder, $filter, false, true); //array of fullpath names of filters
			foreach ($files as $file){
				//$testvar = JFile::getName($file);  // ?problems with JFile::getName()? //
				$name = explode('.',basename($file));
				$base = $name[0];			
				require_once($file);
				$class = 'filter'.ucfirst($base);
				$this->files = $file;
				$filter_obj =& new $class();
				$type = $filter_obj->type; //looking for input/output/raw, etc
				$this->filter->$type->$base =& $filter_obj;
			
			}//end foreach	
	 }//end loadFilters
	 
	 /**
	 *	cycle through filters on user input and return type
	 */
	 
	 function buildKeywords($type = 'string', $term_raw = '--nothing--'){
	 
	 	if($term_raw == '--nothing--'){
			 	$term_raw = JRequest::getVar('terms',null);
		}
	//	if(!empty($term_raw)){
	//		$reg =& JRegistry::getInstance('','bookstore');
	//		$term_raw = $reg->getVar('bookstore.search.terms','');
	//	}
		//var_dump($term_raw);
		//var_dump($type);
	 	$jfilter = & JFilterInput::getInstance();
		if(!isset($this->filter)){
			$this->loadFilters();
		}//end if empty query
	 	
	 	switch($type){
			case 'array':  	// using extended form //
				
				/**
				* use basic Joomla filtering on user input
				*/
					foreach($term_raw as $key=>$value){	//var_dump($value);
						if(is_int($value)){$type = 'INT';}else{$type = '';};
						$this->_terms_array[$key] = $jfilter->clean($value,$type);
					}//edn foreach raw term	//var_dump($this->_terms_array);
				////////////////////////////////////////////////
				
				/**
				* use custom filtering on user input
				*/
					foreach($this->filter->input as $input_filter){
						$this->_terms_array =& $input_filter->filter('array',$this->_terms_array);	
					}//end foreach input filter		//var_dump('done looping filters');
				/////////////////////////////////////////////////
				
				/**
				* parse 'multi term'-per-field into a single value array per field
				*/					
					foreach($this->_terms_array as $k=>$v){
						if(!empty($v)){
							$value = preg_replace('/\s\s+/', ' ', $v);
							$refined[$k] = explode(' ',$v);
						}else{
							$refined[$k] = array();
						}//end if
					
					}		
					$this->_terms_array = $refined;				
				///////////////////////////////////////////////
				
				break;
				
			case 'string': //return str
			default:
				$term_clean = $jfilter->clean($term_raw);
				//var_dump($term_raw);
				$this->_terms_str =& $term_clean;
				foreach($this->filter->input as $input_filter){
					$this->_terms_str =& $input_filter->filter('str',$this->_terms_str);
				}//end foreach input filter
				$reg =& new JRegistry('bookstore');
			//	$reg =& JRegistry::getInstance('global','bookstore');
				$reg->setValue('bookstore.search.terms',$this->_terms_str);
				break;
		}//end switch
		
	 }//end buildKeyword method
	 
/////////////////////////////////////////////////////////////////////////////
	 /**
	 *	get user input to better define sql search
	 *  TODO: either expand this into somethine more useful or incoporate into filters/keywordBuild or assemblyQuery
	 */
	function searchParams($instock_only){
		//$instock_only = JRequest::getVar('instock_only', NULL);
		if( $instock_only == 0 ){
			 $extra_where = ' AND a.avail_for_sale > 0 ';
		}else{
			 $extra_where = '';
		}
		 return $extra_where;
	}//end searchParams method
/////////////////////////////////////////////////////////////////////////////
	 /**
	 *	get user input to better define sql search
	 *  TODO: either expand this into somethine more useful or incoporate into filters/keywordBuild or assemblyQuery
	 */
	function staffselectionJoin(){
		$ssj = ' left join #__staffselectionindex as d on d.isbn=a.isbn left join #__staffselection as c on c.id=d.selectionid  ';
		 return $ssj;
	}//end searchParams method
///////////////////////////////////////////////////////////////////////////////

	 /**
	 *	make query based on user input and filters
	 */
	
	 function assembleQuery($type = '1',$fields = array()){
	 	//var_dump('assemble');
	 	if(empty($this->SQL_helper)){
			$this->SQLhelper();
		}
	 	$fields_str = 'a.*,'
					.$this->SQL_helper->sq1->pub_date_format
					.'b.alias as section_alias';
					
		$dept_cat_number = 'dept_cat_number';
		$order_by = '';
		
		
	 	switch($type){ //some forms of SELECT may be inherently better, depending on the db & fields being queried
			case '1':
			
				///testing 040308
				/*
				SELECT distinct( match(a.title,a.author,a.isbn) against('daniel silva'  WITH QUERY EXPANSION)/3 + ( (MATCH(a.title ) AGAINST('daniel silva' ) * 4 )+ ( MATCH(a.author) AGAINST('daniel silva' ) *4.5) + ( if( c.selectionid > 0,10,0)  ) + 1)/2 ) as score,a.*,STR_TO_DATE(CASE WHEN CHAR_LENGTH(a.pub_date) < 2 THEN '' WHEN CHAR_LENGTH(a.pub_date) < 4 THEN CONCAT('0',substring(a.pub_date,1,1),'/',IF( CAST(substring(a.pub_date,-2,2) AS UNSIGNED INTEGER) < 70, CONCAT('20',substring(a.pub_date,-2,2)), CONCAT('19',substring(a.pub_date,-2,2)) ) ) WHEN CHAR_LENGTH(a.pub_date) = 4 THEN CONCAT( substring(a.pub_date,1,2),'/',IF( CAST(substring(a.pub_date,-2,2) AS UNSIGNED INTEGER) < 70, CONCAT('20',substring(a.pub_date,-2,2)), CONCAT('19',substring(a.pub_date,-2,2)) ) ) ELSE a.pub_date END,'%m/%Y' ) as pub_date,b.alias as section_alias, if( c.selectionid > 0,10,0) as selection FROM jos_sq1 as a left join jos_locator as b ON a.dept_cat_number=b.id left join jos_staffselectionindex as c using (isbn) WHERE match(a.title,a.author,a.isbn) against('daniel silva' IN BOOLEAN MODE)  AND a.avail_for_sale > 0 ORDER BY score desc,selection asc,a.avail_for_sale desc,a.item_number asc,a.retail_price_decimal asc  limit 8;
				*/
			//	$order_by = $this->SQL_helper->sq1->order_by->score_subsection_price;
				$order_by = $this->SQL_helper->sq1->order_by->score3_score2_score_avail_subsection;
				//$order_by = $this->SQL_helper->sq1->order_by->score_avail_subsection;
				//var_dump($this->_terms_str);
				if(!isset($this->_terms_str)){
					$this->buildKeywords('string');
				}
				//var_dump($this->_terms_str);
				if($this->_terms_str<>" " and strlen($this->_terms_str) > 0){
					$extra_where = $this->searchParams();
					/////
					$terms_str = $this->_terms_str;
					//'concat( trim(substring_index(a.author, ',',-1)),' ', trim(substring_index(a.author, ',',1)) )as name'
					if(!preg_match('/[+-]/', $terms_str) ){
						$terms_strr = str_replace(' ',' +', $terms_str);
					}
					// first find total hits
					$terms_str = $this->db->Quote($terms_str);
					$this->short_query = 'SELECT COUNT( '
						.'MATCH(a.title,a.author,a.isbn) AGAINST('
						. $terms_str
						.'  WITH QUERY EXPANSION) )as count'						
						.' FROM '
						.$this->_book_db
						.' as a WHERE '
						.'match(a.title,a.author,a.isbn) against('
						. $terms_str
						.' IN BOOLEAN MODE) '
						.$extra_where ;
					
					/////
					// second get actual result object
					$this->query = 'SELECT distinct match(a.title,a.author,a.isbn) against('
							. $terms_str
							.'  WITH QUERY EXPANSION) as score,'
							.$fields_str
							//title
							.', MATCH(a.title) AGAINST('
							. $terms_str
							.' ) as score3'
							//author name
							.', MATCH(a.author) AGAINST('
							. $terms_str
							.' ) as score2,'
							//selection modifier
							.' if( c.selectionid > 0,10,0) as selection'
							.' FROM '.$this->_book_db
							.' as a left join #__locator as b ON a.'
							.$dept_cat_number 
							.'=b.id'
							.' left join #__staffselectionindex as c using (isbn)'
							.' WHERE '
							.'match(a.title,a.author,a.isbn) against('
							.$terms_str
							.' IN BOOLEAN MODE) '
							.$extra_where
							.$order_by; //will contain string of multiple sorts and direction eg: 'score desc,item_number asc,retail_price_decimal asc'
							
				}//end if $this->_terms_str is not null or empty or whitespace
			
				break;
			case '2': //for advanced query
				$fields_str = 'a.*,'
					.$this->SQL_helper->sq1->pub_date_format
					.'b.alias as section_alias';
					
				$dept_cat_number = 'dept_cat_number';
				$order_by = '';
				$qfields = '';
				$qlike = '';
				//$order_by = 'author';
				$order_by = 'score';
				$direction = 'desc';
				
				if(empty($fields)){
					$this->db->setQuery( 'desc '.$this->_book_db );
					$fields = $this->db->loadResultArray();
				}

				foreach($fields as $key=>$value){
					if( in_array($value,array_keys($this->_terms_array)) && !empty($this->_terms_array[$value]) ){
						$qlike .= '( '. $value.' LIKE ';
						foreach($this->_terms_array[$value] as $k=>$v){
							$pattern[] = '/^"/';
							$pattern[] = '/"$/';
							$v_minus_quote = preg_replace($pattern, '', $v);
							$v_like = "%".$v_minus_quote."%";
							$v_like = $this->db->Quote($v_like);
							if( preg_match_all($pattern,$v) >= 2){
								$v = substr($v,0,1).$this->db->Quote(substr($v,1,-1)).substr($v,-1);
							}else{
								$v = $this->db->Quote($v_minus_quote);
							}
							
							if($k === 0 && count($this->_terms_array[$value]) > 1 ){
								$score_match .= " match("
												.$value
												.")against("
												.$v
												." IN BOOLEAN MODE) +";
								$qlike .= $v_like. ' ) OR ( '. $value.' LIKE ';
							}elseif($k === 0 && count($this->_terms_array[$value]) == 1){
								$score_match .= " match("
												.$value
												.")against("
												.$v
												." IN BOOLEAN MODE) +";
								$qlike .= $v_like. ' OR ';				
							}else{
								/* //code for negating multi-terms with boolean
								if(strpos($v_like,'-') === 1){//using the '-' boolean operator
									$v_like = str_replace('%-','%',$v);
									if($k === 1){ //first term
										//$qlike = substr($qlike,0,-5).' NOT LIKE ';
										$join = ' ) AND ( '.$value.' LIKE ';
										$qlike = substr($qlike,0,-5).' NOT LIKE '.$v_like.$join;
									
									}else{// second term or higher
									
										$join = ' ) AND ( '.$value.' NOT LIKE ';
										$qlike = substr($qlike,0,-5).' NOT LIKE '.$v_like.$join;
									}
									
								}else{
									$join = ' OR ';
									$qlike = $v_like.$join;
								
								}
								*/
								$qlike .= $v_like. ' OR ';
							}
						}//end foreach in terms
						$qlike = substr($qlike,0,-4);
						$qlike .= ') AND ';
					}//end if array
				
				}//end foreach in fileds
				$score_match = ", ( ".substr($score_match,0,-1)." ) as score ";
				$qlike = substr($qlike,0,-5);
				
				///////////////////////////////////////
				// order by clause
				$order = array(
						'0'	=>	'author',
						'1'	=>	'title',
						'2'	=>	'isbn',
						'3'	=>	'pub_date',
						'4'	=>	'pub_name',
						'5'	=>	'',
						'6'	=>	'',
						'7'	=>	'',
						'8'	=>	'',
						'9'	=>	''
						);
				//$qorder = 'ORDER BY '.$order_by.' '.$direction;
				$qorder = $this->SQL_helper->sq1->order_by->score_avail_subsection;
				$extra_where = $this->searchParams($this->_terms_array['instock'][0]);
				////////////////////////////////////////
				$this->query = 'SELECT '
				.$fields_str
				.$score_match
				.' from '
				.$this->_book_db
				.' as a left join #__locator as b ON a.'
				.$dept_cat_number 
				.'=b.id'
				.' WHERE ('
				.$qlike.')'
				.$extra_where
				.$qorder;
				
				$this->short_query = 'SELECT '
				//.$fields_str
				. 'COUNT( '
				.substr($score_match,4,-6)
				. 'count'
				.' from '
				.$this->_book_db
				.' as a left join #__locator as b ON a.'
				.$dept_cat_number 
				.'=b.id'
				.' WHERE ('
				.$qlike.')'
				.$extra_where
				;
				//.$qorder;
				
				break;
			
			case '3': //for advanced query
				$fields_str = 'a.*,'
					.$this->SQL_helper->sq1->pub_date_format
					.'b.alias as section_alias,'
					.'c.title as staffselection_title,d.selectionid';
					
				$dept_cat_number = 'dept_cat_number';
				$order_by = '';
				$qfields = '';
				$qlike = '';
				//$order_by = 'author';
				$order_by = 'score';
				$direction = 'desc';
				
				if(empty($fields)){
					$this->db->setQuery( 'desc '.$this->_book_db );
					$fields = $this->db->loadResultArray();
				}

				foreach($fields as $key=>$value){
					if( in_array($value,array_keys($this->_terms_array)) && !empty($this->_terms_array[$value]) ){
						$qlike .= '( a.'. $value.' LIKE ';
						foreach($this->_terms_array[$value] as $k=>$v){
							$v = $this->db->Quote($v);
							$v_like = "%".$v."%";
							$v_like = $this->db->Quote($v_like);
							if($k === 0 && count($this->_terms_array[$value]) > 1 ){
								$score_match .= " match(a."
												.$value
												.")against("
												.$v
												." IN BOOLEAN MODE) +";
								$qlike .= ".$v_like.". ' ) OR ( a.'. $value.' LIKE ';
							}elseif($k === 0 && count($this->_terms_array[$value]) == 1){
								$score_match .= " match(a."
												.$value
												.")against("
												.$v
												." IN BOOLEAN MODE) +";
								$qlike .= $v_like. ' OR ';				
							}else{
								
								$qlike .= $v_like. ' OR ';
							}
						}//end foreach in terms
						$qlike = substr($qlike,0,-4);
						$qlike .= ') AND ';
					}//end if array
				
				}//end foreach in fileds
				$score_match = ", ( ".substr($score_match,0,-1)." ) as score ";
				$qlike = substr($qlike,0,-5);
				
				///////////////////////////////////////
				// order by clause
				$order = array(
						'0'	=>	'author',
						'1'	=>	'title',
						'2'	=>	'isbn',
						'3'	=>	'pub_date',
						'4'	=>	'publisher',
						'5'	=>	'',
						'6'	=>	'',
						'7'	=>	'',
						'8'	=>	'',
						'9'	=>	''
						);
				$qorder = 'ORDER BY '.$order_by.' '.$direction;
				$extra_where = $this->searchParams($this->_terms_array['instock'][0]);
				$staffselection_join = $this->staffselectionJoin();
				////////////////////////////////////////
				$this->query = 'SELECT '
				.$fields_str
				.$score_match
				.' from '
				.$this->_book_db
				.' as a left join #__locator as b ON a.'
				.$dept_cat_number 
				.'=b.id'
				.$staffselection_join
				.' WHERE ('
				.$qlike.')'
				.$extra_where
				.$qorder;
				$this->short_query = $this->query;
				break;
		
			default: //default, just make a simply 'like' structured query
				
				$qfields = '';
				$qlike = '';
				$order_by = 'author';
				$direction = 'asc';
				//var_dump('defaultassemble');
				//$this->_terms = $this->_terms_array;
				//TODO prefilter each k=>v into field=>array_of_values for multiple keywords per field
				if(empty($fields)){
					$this->db->setQuery( 'desc '.$this->_book_db );
					$fields = $this->db->loadResultArray();
				}
				//var_dump($this->_terms_array);
				foreach($fields as $key=>$value){
				//var_dump($this->_terms_array[$value]);
				// 
					if( in_array($value,array_keys($this->_terms_array)) && !empty($this->_terms_array[$value]) ){
					//	$qfields .= $value.',';
						$qlike .= '( '. $value.' LIKE ';
						foreach($this->_terms_array[$value] as $k=>$v){
							if($k === 0 && count($this->_terms_array[$value]) > 1 ){
								$qlike .= "'%".$v."%'". ' ) OR ( '. $value.' LIKE ';
							}else{
								$qlike .= "'%".$v."%'". ' OR ';
							}
						}//end foreach in terms
						$qlike = substr($qlike,0,-4);
						$qlike .= ') AND ';
					}//end if array
				
				}//end foreach in fileds
			//	$qfields = substr($qfields,0,-1);
				$qlike = substr($qlike,0,-5);
				$qorder = 'ORDER BY '.$order_by.' '.$direction;

				$this->query = 'SELECT * from '
				.$this->_book_db
				.' WHERE ('
				.$qlike.')'
				.$qorder;
				$this->short_query = $this->query;
				break;
		
		
		}//end switch
	 	
	 }//end assembleQuery method
	 
	 
	 /**
	 * perform query and save result
	 */
	 function queryDb(){				
	 	if(empty($this->query)){
			//var_dump('trouble');
			$this->assembleQuery(1);
		}//end if empty query // would recursively(thru model functions) build a query from user input
		
		//find hits for navigation
		// added 'limit 200' as disguise for main queries seemingly endless results
		$this->navigator($this->short_query.' limit 200');
		//var_dump($this->short_query);
		//perfom full query //var_dump($this->query);
		//var_dump($this->_terms_array);
		//var_dump($this->short_query);
		//var_dump($this->query);
		$this->_results = $this->_getList($this->query,$this->pageNav->limitstart, $this->pageNav->limit );
			//NOTE:  do I need to had error processing for these db calls, or will Joomla handle them?
			//		if ($this->db->getErrorNum()) {
			//			echo $this->db->stderr();
			//			return;
			//		}//end error if
	 }//end method queryDb
	 
	 /**
	 * pageNav of this object/model
	 */
	 function navigator($query){ //$query is a short query for getting hit count; no sort necessary
	 	global $mainframe, $option;
		$this->db->setQuery($query);
		$this->total = $this->db->loadResult();
		$limit = $mainframe->getUserStateFromRequest("com_bookstore.query.limit", 'limit', $mainframe->getCfg('list_limit'), 0);// $limit = 20;//for frontend
		$limitstart	= $mainframe->getUserStateFromRequest('com_bookstore.query.limitstart', 0, '', 'int');
		jimport('joomla.html.pagination');
		$this->pageNav = new JPagination( $this->total, $limitstart, $limit );
		////////////// not sure about this code? looks like its for backend/ copied from admin /////////
		//$pageNav = new JPagination( 385, 0, 20 );
		//echo $pageNav->getPagesLinks( JURI::base() . "index.php?option=$option" );
		//jimport('joomla.presentation.pagination');
		//$this->pageNav = new JPagination( $this->total, $limitstart, $limit );
		//echo $pageNav->getPagesLinks( JURI::base() . "index.php?option=$option" );
		//(empty($this->limit))?$limit = 25 : $limit = $this->limit;
		////////////////////////////////////////////////////////////////////////////////////////////////
	 }//end navigator method
	 /*
	 function saveRequest($request = array() ){
				global $mainframe,$option;
				foreach($request as $field=>$array){
					foreach($array as $index=>$value)
						$mainframe->setUserState( "$option.terms.$field.$index", $value );		
					}//end foreach index/value
				}//edn foreach $field/arrayofvalues
	 }//end saveRequest method
	 */
	 
	 function inspectRequest(){
	 	
	 	//$method = JRequest::getMethod();
	 	//(strtolower($method) == 'post')$var = "$terms['']"
	 	$query_type = JRequest::getVar('query_type', NULL);
	 	if($query_type == 'Search'){
			$this->handleRequest(1);
			return 0;
		}elseif($query_type == 'Advanced Query'){
			$this->handleRequest(2);
			return 0;
		}elseif($query_type == 'pageNav'){
			$this->handleRequest(3);
			return 0;
		}else{
			$this->handleRequest(0);
			return 1;
			//$terms['query_type'] = 'prestine';
		}
	 }//end inspectRequest method
	
	 function handleRequest($type = 0, $term_raw = '--nothing--'){ 
	 	//evaluate incoming request or saved user state
	 	//$type determines if it was a simple(1) or advanced(2) or pageNav(3) query
		//$term_raw gives code an injection point for (weird?) flexibility
		global $mainframe,$option;
		
	 	switch($type){
			case '1': //simple search; incoming str into terms array	
				if($term_raw == '--nothing--'){
				 	$term_raw = JRequest::getVar('terms',null); //this will be a string
				}				
				///////////////////////////
				// dice up string on expect user structure (spaces, maybe even a GET)
				///////////////////////////
				/*
				if(!empty($term_raw)){
					$k = 'simple';
				
					if(strpos($term_raw['simple'], ' ')){
						$value = preg_replace('/\s\s+/', ' ', $term_raw['simple']);		//replace excess spaces
						$array = explode(' ',$value);						//make an array
						array_unshift($array, $term_raw);						//insert orig str at key zero
						$term_raw[$k] = $array;
					}else{
						$term_raw[$k] = array($term_raw['simple']);
					}	//end if multi term in field
				}else{
					$k = 'simple';
					$term_raw[$k] = array();
				}//end if
				*/
				//$term_raw_array['offset'] = 0;
				///////////////////////////
				// now that we've divided the string and placed into an array, break and filter it
				///////////////////////////
				break;
				
			case '2': //advanced query; store terms and getdata
				if($term_raw == '--nothing--'){
				 	$term_raw = JRequest::getVar('terms',null); //this will be a string
				}
				//var_dump('2advanced');
				/////////////////////////////////////////////
				// can't we do this at a more appropriate place?
				$term_raw['offset'] = 1;
				//$term_raw_array = $term_raw;
				///////////////////////////
				// terms was already an array, break and filter it
				///////////////////////////
				break;
				
			case '3': //pageNav; set limitstart and getdata
				$limitstart = JRequest::getVar('limitstart', 0);
				$mainframe->setUserState( "com_bookstore.query.limitstart", $limitstart ); 
				/////////////////////////////////////////////////////////////////
				//terms already set&stored, revise results show by moving record offset in SQL (which should be build into assembleQuery method)
				/////////////////////////////////////////////////////////////////
				//var_dump('3pagenav');
				$this->_terms_array = $mainframe->getUserState( "com_bookstore.query.terms", '' ); 
				return;
				break;
				
			case '0': //empty search, landing page
			default:
				return;
		}//end switch

		////////////////////////////////////////////
		// must store terms into mainframe/session/registry; when this method exits, model must use the stored values from that point forward!
		/////////////////////////////////////////////
		
		/**
		* use basic Joomla filtering on user input
		*/
			//var_dump($term_raw);
			$jfilter = & JFilterInput::getInstance();
			foreach($term_raw as $key=>$value){	//var_dump($value);
				if(is_int($value)){$type = 'INT';}else{$type = '';};
				$this->_terms_array[$key] = $jfilter->clean($value,$type);
			}//edn foreach raw term	//
			//var_dump($this->_terms_array);
		////////////////////////////////////////////////
		
		/**
		* use custom filtering on user input
		*/
			if(!isset($this->filter)){
				$this->loadFilters();
			}//end if empty query
			foreach($this->filter->input as $input_filter){
				$this->_terms_array =& $input_filter->filter('array',$this->_terms_array);	
			}//end foreach input filter		//var_dump('done looping filters');
			//var_dump($this->_terms_array);
		/////////////////////////////////////////////////
		
		/**
		* parse 'multi term'-per-field into a single value array per field
		*/					

			foreach($this->_terms_array as $k=>$v){
				if(!empty($v)){
					if(strpos($v, ' ')){
						$value = preg_replace('/\s\s+/', ' ', $v);		//replace excess spaces
						$array = explode(' ',$v);						//make an array
						array_unshift($array, $v);						//insert orig str at key zero
						$refined[$k] = $array;
					}else{
						$refined[$k] = array($v);
					}	//end if multi term in field
				}else{
					$refined[$k] = array();
				}//end if
			
			}		
			$this->_terms_array = $refined;				
			//var_dump($this->_terms_array);
		///////////////////////////////////////////////
		////////////////////////////////////////////////////////////
		// remove all old variables
		$mainframe->setUserState( "com_bookstore.query", '' );
		//$mainframe->setUserState( "com_bookstore.query", '' );
		
		////////////////////////////////////////////////////////////
		// finally store into registry for model to access
		$mainframe->setUserState( "com_bookstore.query.terms", $this->_terms_array ); //"$option.terms"
		$mainframe->setUserState( "com_bookstore.query.orig_terms", $term_raw );
		//var_dump($term_raw);
		$limitstart = JRequest::getVar('limitstart', 0);
		$mainframe->setUserState( "com_bookstore.query.limitstart", $limitstart ); 
		
	 }//end handleRequest method
	 
		
	 /**
	 *	return results after applying output 'filters'
	 */
	 function returnResults(){
	 	if(!isset($this->filter)){
			$this->loadFilters();
		}//end if empty query
		
		foreach($this->filter->output as $output_filter){
			$this->filtering++;
			$this->_filtered_results =& $output_filter->filter('',$this->_results);
		}//end foreach input filter
		
		// apply staff selections
		// TODO: there's got to be a better way
		//   maybe in the sql statement?
		foreach($this->_filtered_results->result_set as $key=>$value){
			$value->sq1->reviews =& $this->getSelections($value->isbn);
		}
		return $this->_filtered_results;
	 }//end returnResults

	function getLocalResults(){
		if (empty( $this->_data )){
			$terms =& JRequest::getVar('terms', NULL);
			//could just call querydb, but well write it out for posterity
			$this->loadFilters(); //loads filter obj with filters
			
			if(is_array($terms)){
				if($terms['query_type'] == 'prestine'){
					$this->buildKeywords('string', NULL );
					$this->assembleQuery();
				}elseif($terms['offset'] == 1){ //advanced extended form query
					$this->buildKeywords('array');
					$this->assembleQuery('array'); 
				}elseif($terms['offset'] === 0){//quick search extended form query
					$term_raw = JRequest::getVar('terms', '');
					$this->buildKeywords('string',$terms['simple']);
					$this->assembleQuery();
				}
			}elseif(is_object($terms)){
				$this->buildKeywords('object');
				$this->assembleQuery(); //creates sql statement
			}else{
				$this->buildKeywords(); //takes user input and preps it (with filters and syntax/typecasting)
				$this->assembleQuery(); //creates sql statement
			}
			
			$this->queryDb(); //runs sql query
			$this->_data =& $this->returnResults(); //applies any output filters and preps data for view obj

			///////// old /////////
			//$site = $this->_setQueryEngine('','');
            //$this->_runCurl($site);
			//$this->_data =& $this->_parseResults();
			///////////////////////
        }//end if _data
		return $this->_data;
	}//end localresults

function getLocalResults2($custom_terms = ''){
		if (empty( $this->_data )){
			if(empty($custom_terms)){
				global $mainframe;
				$terms = $mainframe->getUserState( "com_bookstore.query.terms", '' ); 
				//var_dump($terms);	
				if(is_array($terms['offset'])){
					$offset = $terms['offset'][0];
				}else{
					$offset = $terms['offset'];
				}
			}else{
				
				$terms = $custom_terms;
				$offset = $terms['offset'];
			}
			//could just call querydb, but well write it out for posterity; NOTE: in getLocalResults2 this would probably generate unexpected results
			
		//	$this->loadFilters(); //loads filter obj with filters
		//	if(is_array($terms)){
				if($terms['query_type'] == 'prestine'){
				//	$this->buildKeywords('string', NULL );
				//	$this->assembleQuery();
				}elseif($offset == 1){ //advanced extended form query
					//$this->buildKeywords('array');  //most all of this should have happened in inspect/handle request methods
					//$this->assembleQuery('array'); 
					$this->assembleQuery(2); 
				//	$this->assembleQuery(3); //adds staffselection_join clause (line 174) 
				//NOTE: problem with this staffselection_join is that it will only retrieve the first ss_id, not multiple
				//  so much for a better method...(line 676)
				}elseif($offset == 0){//quick search extended form query
				//	$term_raw = JRequest::getVar('terms', '');
				//	$this->buildKeywords('string',$terms['simple']);
				//var_dump('here');//$this->_terms_array['simple'][0]);
				$array = $this->_terms_array['simple'];
				if(count($array) > 1){
					array_shift($array); //removes the raw string(array[0]) and leaves any 'words'(which start in array[1])
				}
				$this->_terms_str = implode(' ',$array);
				//var_dump($this->_terms_str);
					$this->assembleQuery(1);
				}
		//	}elseif(is_object($terms)){ //oops how did we get here; this hasn't been written yet!
		//		$this->buildKeywords('object');
		//		$this->assembleQuery(); //creates sql statement
		//	}else{
		//		$this->buildKeywords(); //takes user input and preps it (with filters and syntax/typecasting)
		//		$this->assembleQuery(); //creates sql statement
		//	}
			echo '<!-- ';
			$replaced_query = str_replace('#_','jos',$this->query).' limit 6;';
			var_dump($replaced_query);
			echo ' -->';
			//var_dump($this->query);
			$this->queryDb(); //runs sql query
			$this->_data =& $this->returnResults(); //applies any output filters and preps data for view obj

			///////// old /////////
			//$site = $this->_setQueryEngine('','');
            //$this->_runCurl($site);
			//$this->_data =& $this->_parseResults();
			///////////////////////
        }//end if _data
		return $this->_data;
	}//end localresults



/////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////// SQL helpers ////// NOTE: should might want to relocate to helper file if it gets too big, or /////
/////////////////////////////  better may a SQL procedure/function?										/////
/////////////////////////////////////////////////////////////////////////////////////////////////////////////
	function SQLHelper(){
		//makes an helper object with stored statements in $this->SQL_helper (that SQL not SQ[one])
		// know children: sq1 (thats SQ[one])
			
			//rather unreasonable measures to deal with SQ1's publishing date, will output yyyy-mm-dd in 'pub_date' column
			$this->SQL_helper->sq1->pub_date_format = ''
					.'STR_TO_DATE('
						.'CASE '
						.'WHEN CHAR_LENGTH(a.pub_date) < 2 '
							."THEN '' "
						.'WHEN CHAR_LENGTH(a.pub_date) < 4 '
							.'THEN '
								.'CONCAT('
									."'0',"
									.'substring(a.pub_date,1,1),'
									."'/',"
									.'IF( '
										.'CAST(substring(a.pub_date,-2,2) AS UNSIGNED INTEGER) < 70, '
										."CONCAT('20',substring(a.pub_date,-2,2)), "
										."CONCAT('19',substring(a.pub_date,-2,2))"
									.' ) ) '
						.'WHEN CHAR_LENGTH(a.pub_date) = 4 '
							.'THEN '
								.'CONCAT( '
									.'substring(a.pub_date,1,2),'
									."'/',"
									.'IF( '
										.'CAST(substring(a.pub_date,-2,2) AS UNSIGNED INTEGER) < 70, '
											."CONCAT('20',substring(a.pub_date,-2,2)), "
											."CONCAT('19',substring(a.pub_date,-2,2))"
									.' ) ) '
						.'ELSE a.pub_date END,'
					."'%m/%Y' ) as pub_date,";
					
			// defacto sort with MATCH as score
			$this->SQL_helper->sq1->order_by->score_subsection_price = 'ORDER BY '
							.'score desc,'
							.'a.item_number asc,'
							.'a.retail_price_decimal asc ';
							
			$this->SQL_helper->sq1->order_by->score_avail_subsection = 'ORDER BY '
							.'score desc,'
							.'a.avail_for_sale desc,'
							.'a.item_number asc,'
							.'a.retail_price_decimal asc ';
							
			$this->SQL_helper->sq1->order_by->score3_score2_score_avail_subsection = 'ORDER BY '
							//.'score2 desc,'
							.'score desc,'
							.'selection asc,'
							//.'score2 desc'
							//.'score3 desc,'
							.'a.avail_for_sale desc,'
							.'a.item_number asc,'
							.'a.retail_price_decimal asc ';
			
	}//end of SQLHelper method
////////////////////////////////  sample SQL statements ////////////////////////////////////////
//simply provice a boolean or natural keyword string
				//select distinct match(title,author,isbn)against('lolita nabokov') as score, title, bk_class_desc from jos_sq1 where title like '%lolita%' or author like '%lolita%' or title like '%nabokov%' or author like '%nabokov%' ORDER BY score desc,item_number asc,retail_price_decimal asc limit 20
				//SELECT distinct match(a.title,a.author,a.isbn)against('amber spyglass') as score,a.*,b.alias as section_alias FROM jos_sq1 as a left join jos_locator as b on a.dept_cat_number=b.id WHERE match(a.title,a.author,a.isbn)against('amber spyglass' IN BOOLEAN MODE) ORDER BY score desc,a.item_number asc,a.retail_price_decimal asc limit 10;
				//SELECT distinct match(a.title,a.author,a.isbn) against('Tom Clancy') as score,a.title,a.isbn, STR_TO_DATE(CASE WHEN CHAR_LENGTH(a.pub_date) < 2 THEN '' WHEN CHAR_LENGTH(a.pub_date) < 4 THEN CONCAT('0',substring(a.pub_date,1,1),'/',IF( CAST(substring(a.pub_date,-2,2) AS UNSIGNED INTEGER) < 70, CONCAT('20',substring(a.pub_date,-2,2)),CONCAT('19',substring(a.pub_date,-2,2)) ) ) WHEN CHAR_LENGTH(a.pub_date) = 4 THEN CONCAT( substring(a.pub_date,1,2),'/',IF( CAST(substring(a.pub_date,-2,2) AS UNSIGNED INTEGER) < 70, CONCAT('20',substring(a.pub_date,-2,2)),CONCAT('19',substring(a.pub_date,-2,2)) ) ) ELSE a.pub_date END,'%m/%Y' ) as pub_date,b.alias as section_alias FROM jos_sq1 as a left join jos_locator as b ON a.dept_cat_number=b.id WHERE match(a.title,a.author,a.isbn) against('Tom Clancy' IN BOOLEAN MODE) AND a.avail_for_sale > 0 ORDER BY score desc,pub_date desc,a.item_number asc,a.retail_price_decimal asc


////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////// code refactor from phase 1 //////////////////////////////////////////////	 
////////////////////////////////////////////////////////////////////////////////////////////////
	 function getSelections($isbn){
	 
	 	$query = "SELECT a.isbn,b.*,c.title as cat_title FROM #__staffselectionindex as a left join #__staffselection as b left join #__categories as c on b.catid = c.id on a.selectionid = b.id WHERE a.isbn = ".$this->db->Quote($isbn)." and b.state > 0 and c.published > 0";
		//var_dump($query);
		$this->db->setQuery( $query);
		$indices =& $this->db->loadObjectList();
		//var_dump($indices);
		return $indices;
		
	 }
	 
/////////////////////////////////////////////////////////////////////////////
/////////////////// older code, mostly legacy, but need and used by API /////
/////////////// refactor ASAP ///////////////////////////////////////////////
	 /**
	 * setEngine is a pre-query function; set the 1st tier query engine
	 *  based on INI or DB data
	 *  NOTE: currently, it does not get data, but is hardcoded to BookSite
	 */
	 
	 function _setQueryEngine($site, $terms)
	{
		//set terms
		if(!empty($terms)){
			$termstr = $terms;
		}else{
			$termstr = JRequest::getVar('terms');
			//var_dump($termstr);
			$termstr = str_replace(' ','+',$termstr);
		}//end if terms were provided via arg
		
		//set site to query
		if(!empty($site)){
			$site_to_query = $site;
			$site_to_query .= '&isbn='.$termstr;
		}else{
			$site_to_query = 'http://www.booksite.com/texis/scripts/oop/rawsearch_ing?sid=3401';
			$site_to_query .= '&key='.$termstr;
		}
		return $site_to_query;	
	}
	 
	 /**
	 * runCurl performs the cURL action
	 *  
	 */
	 
	 function _runCurl($site_to_query, $return = FALSE)
	{
	 	$this->ch = curl_init();
		curl_setopt($this->ch, CURLOPT_URL, $site_to_query);
		curl_setopt($this->ch, CURLOPT_RETURNTRANSFER, 1); 
		if($return){
			$results = curl_exec($this->ch);
			curl_close($this->ch);
			return $results;
		}else{
			$this->_results = curl_exec($this->ch);
			curl_close($this->ch);
		}
		//var_dump('closed');
	}
	
	 /**
	 * parseResults parses the initial request
	 * 
	 * NOTE: for now, it hardcoded for booksite, in future, this 
	 * switch based on _setEngine; maybe call other component(booksite)
	 *  
	 */
	 
	 function _parseResults()
	{
		//var_dump($this->_results);
		//$this->_results = trim($this->_results);
		$document_rows = explode("\n",$this->_results);
//		var_dump($list);
//		$document_array = explode('|',$this->_results);
		//var_dump($document_array);
		//warehouse|Title|Author|PUB_DATE|ISBN|Binding|Price|Disc_Key|Detailpage(Y/N)|
		$booksite_legend_1 = array(
							'0'	=>	'warehouse',
							'1'	=>	'title',
							'2'	=>	'author',
							'3'	=>	'pub_date',
							'4'	=>	'isbn',
							'5'	=>	'binding',
							'6'	=>	'price',
							'7'	=>	'disc_key',
							'8'	=>	'detailpage'
							);
	
		$object = new stdclass();
		//$object->result_set = new stdclass();
		$object->result_set = array();
//		$header = array_shift($document_array);
		$header = array_shift($document_rows);
//		$object->rows = array_shift($document_array);
		$object->rows = array_shift($document_rows);
		$object->rows = substr($object->rows,0,-1);
		//var_dump($document_rows);
		foreach($document_rows as $k=>$v){
			//$value = ltrim($value);
			if(!empty($v)){
				$array =  explode('|',$v);
				array_pop($array);
				$chunked[$k] = $array;
			}//end if empty
		}//end foreach
//		$chunked = array_chunk($document_array, 9);
		//var_dump($chunked);
	/*
		foreach($chunked as $key=>$value){ //each row as array
			$object->result_set->$key = new stdclass;
			foreach($value as $column=>$data){ //each column of data in row
				$object->result_set->$key->$booksite_legend_1[$column] = $data;
			}//end of foreach data bit
			
			//detailed booksite
		//	$this->getDetail($object->result_set->$key);
			
			//sq1
			$object->result_set->$key->sq1 =& $this->_parseSq1($object->result_set->$key);
			//staff selection
			
			//calendar
			
			//
		}//end foreach array row
	*/
		/////////////////////////////////////////////
		
		foreach($chunked as $key=>$value){ //each row as array
			//var_dump($value);
			$object->result_set[$key] = new stdclass;
			
			foreach($value as $column=>$data){ //each column of data in row
				if($booksite_legend_1[$column] == 'pub_date'){
					$pub_date = explode('/',$data);
					$epoch = mktime ( 6,0, 0, $pub_date['1'],1 , $pub_date['0']);
					$object->result_set[$key]->epoch = $epoch;	
					$object->result_set[$key]->$booksite_legend_1[$column] = $data;
				}else{
					$object->result_set[$key]->$booksite_legend_1[$column] = $data;
				}
			}//end of foreach data bit
			
			/**
			* get correlative info
			*/
			//detailed booksite
		//	$this->getDetail($object->result_set->$key);
			
			//sq1
			$object->result_set[$key]->sq1 =& $this->_parseSq1($object->result_set[$key]);
			//var_dump($object);
			$object->result_set[$key]->sq1->reviews =& $this->getSelections($object->result_set[$key]->isbn);
			//$object->debug[$key]=$value;
			//staff selection
			
			//calendar
			
			//
		
		}//end foreach array row
		
		/////////////////////////////////////////////
		//var_dump($object);
		$this->sort_by($object, '');
		return $object;
	}
	
	function sort_by(&$obj, $sort_indices = ''){
		//$sort_indices is an array of indices that we may wish to sort by
		// default sort is availability/pub_date
		if(empty($sort_indices)){
			$sort_indices = array('total_avail','epoch');
		}//end if empty
		
   		/* This is the static comparing function: */
			//if ($a->$index == $b->$index) {	return 0; }elseif($a->$index > $b->$index){ return +1; }else{ return -1; }
		$field_name = 'total_avail'; //test value, normally defined by iteration+switch
		$sq1_code = "return strnatcmp(\$a->sq1->instore->$field_name, \$b->sq1->instore->$field_name);";
		usort($obj->result_set, create_function('$b,$a', $sq1_code));
		
		$field_name = 'epoch';
		$code = "return strnatcmp(\$a->$field_name, \$b->$field_name);";
		usort($obj->result_set, create_function('$b,$a', $code));
		
				
		/*
		*/
		//usort($a, array("TestObj", "cmp_obj"));
	}//end sort_by method
	
	function _parseDetails($results)
	{
		//var_dump($this->_results);
		$document_array = explode('|',$results);
		//var_dump($document_array);
		//!!!000|warehouse|Title|Author|Publisher_Name|PUB_DATE|ISBN|Binding|Price|Disc_Key|GRAPHICS|Annotation|
		$booksite_legend_2 = array(
							'0'	=>	'warehouse',
							'1'	=>	'title',
							'2'	=>	'author',
							'3'	=>	'publisher_name',
							'4'	=>	'pub_date',
							'5'	=>	'isbn',
							'6'	=>	'binding',
							'7'	=>	'price',
							'8'	=>	'disc_key',
							'9'	=>	'graphics',
							'10'=>	'annotation'
							);
	
		$object = new stdclass();
		//$object->result_set = new stdclass();
		$header = array_shift($document_array);
		//$object->rows = array_shift($document_array);
		$chunked = array_chunk($document_array, 11);
		//var_dump($chunked);
		foreach($chunked[0] as $key=>$value){ //each row as array
			//var_dump($value);
			$object->$booksite_legend_2[$key] = $value;
			//foreach($value as $column=>$data){ //each column of data in row
			//	$object->result_set->$key->$booksite_legend_1[$column] = $data;
			//}//end of foreach data bit
	 	}//end foreach
		return $object;
	 }

////////////////////////////////////////////////////////////////////////////////////////////////	 
	 /**
	 * use object to find relative sq1 results
	 *  and store them back in object
	 * @var title object
	 */
	 function _parseSq1($object)
	{
		$results =new stdclass();
		
		$query = 'SELECT a.*,b.alias as section_alias FROM #__sq1 as a left join #__locator as b on a.dept_cat_number=b.id WHERE a.isbn="'.$object->isbn.'" ORDER BY a.item_number ASC' ;
	//	$results->query = $query;
		//var_dump($query);
		$results->details = $this->_getList($query);
		if(!empty($results->details)){
		$results->instore = new stdclass();	
			$results->instore->total_avail = 0;
			foreach($results->details as $key=>$value){
				if($value->bk_class_desc != 'MARKDOWN BOOK'){
				$results->instore->total_avail = $results->instore->total_avail + $value->avail_for_sale;
				}//end if
			}//end of foreach
		}else{ 
			/**
			* NOTE: here 'NIS' stands for Never In Stock; in SQ1, it only means Not In Stock;
			*/
			$results->instore = new stdclass();
			$results->instore->flag = (string)'NIS';
			$results->instore->total_avail = (int)'0';
		}//end if results
		
   		return $results;
	}
	
	 function getResults()
	{
		if (empty( $this->_data ))
        {
			$site = $this->_setQueryEngine('','');
            $this->_runCurl($site);
			$this->_data =& $this->_parseResults();
        }
		
		return $this->_data;
	}
	
	function getDetail(&$object)//an entry object, not the object set
	{
		if (!empty($object))
        {
			$object->sq1 =& $this->_parseSq1($object);
			
			////////// TODO: insert parameter call for just onhand/in sq1 at >= 0/all; currently will get based on in sq1 at >= 0
			if(is_object($object->sq1->instore)){
					$detail_site ="http://www.booksite.com/texis/scripts/oop/rawsearch_ing/rawdetail.html?sid=3401";
					$site = $this->_setQueryEngine($detail_site, $object->isbn);
					$results = $this->_runCurl($site, TRUE);
					$object->detail =& $this->_parseDetails($results);
			}else{
				$object->detail = (string)'NIS';
			}//end if searches for only items with record in SQ1
			//}//end foreach that iterate of instore object
        }//end if

	}//end getDetail
	
/////////////////////////////////////////////////////
	 function _setBuild($type = 'Calendar')
    {
		$this->_buildtype = "_build".ucfirst($type);
		//var_dump($this->_buildtype);
		return "_build".ucfirst($type);
	}
	 
    function _buildCalendar()
    {
        $query = 'SELECT a.id, a.title, a.start_date, a.start_time, a.description, a.catid  FROM #__calendar AS a INNER JOIN #__categories AS c ON a.catid = c.id WHERE a.published = 1 ORDER BY a.catid, a.start_date' ;
        return $query;
    }
	
	function _buildDetail()
    {
        $query = 'SELECT a.id, a.title, a.start_date, a.start_time, a.description, a.catid  FROM #__calendar AS a INNER JOIN #__categories AS c ON a.catid = c.id WHERE a.published = 1 and a.id=".." ORDER BY a.catid, a.start_date' ;
        return $query;
    }

    /**
     * Retrieves the hello data
     * @return array Array of objects containing the data from the database
     */
    function getData()
    {
        // Lets load the data if it doesn't already exist
        if (empty( $this->_data ))
        {
			if(empty($this->_buildtype)){
				$type = $this->_setBuild();
			}else{
				$type = $this->_buildtype;
			}
			var_dump($type);
            $calendar = $this->$type();//$this->_buildCalendar();////$this->_build$type();
            $this->_data = $this->_getList( $calendar );
        }
		
		return $this->_data;
		
    }
}
?>
